package hms.database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * The DBRowObject abstract class is for any object whose fields must interact
 * with the database to extend. This class provides common methods for marking
 * your object as changed and for committing your changes to the database.
 * 
 * Your object should have fields corresponding to the columns of the database
 * and methods to interact with these fields. See the Javadocs of the abstract
 * methods to see how to fill them out to ensure the database is properly
 * communicated with.
 * 
 * @author Ben
 * 
 */
public abstract class DBRowObject {
	private boolean validID;
	private int databaseID;
	boolean changed;
	boolean toBeDeleted;
	boolean isDeleted;

	/**
	 * This no-arg constructor creates an object that is not in the database.
	 * <p>
	 * Your constructors should ensure that they contain database-safe data; use
	 * reduceCharset to pare down strings for this purpose.
	 * <p>
	 * Your no-arg constructor should call this constructor and then initialize
	 * the other fields to default values.
	 * <p>
	 * If your constructors create objects that are known to be in the database,
	 * be sure to call markAsSychedWithDatabase().
	 */
	public DBRowObject() {
		validID = false;
		changed = false;
		toBeDeleted = false;
		isDeleted = false;
	}
	
	public DBRowObject(int uniqueID) {
		this();
		DAO localDAO = DAOnoGUI.getDAO();
		String query = "SELECT * FROM " + getTableName() + " WHERE " + getUniqueIdentifierFieldName() + "=" + uniqueID + ";";
		ResultSet rs = localDAO.getResult(query);
		try {
			if(rs.next()) {
				loadFromDatabase(rs);
				setUniqueIdentifier(uniqueID);
				markAsSynchedWithDatabase();
			}
		} catch (SQLException e) {
			// TODO [Ben] Auto-generated catch block
			e.printStackTrace();
		}
		DAOnoGUI.closeDAO();
	}

	/**
	 * Mark this object as having a field changed. Objects marked with this
	 * function will write their changes to the database when the
	 * commitToDatabase function is called.
	 */
	protected void markAsChanged() {
		changed = true;
	}

	/**
	 * Mark this object as having been read or written from/to the database.
	 * These objects have a valid unique key identifier and have not deviated
	 * yet from the database copy. No attempt will be made to write them to the
	 * database.
	 */
	protected void markAsSynchedWithDatabase() {
		validID = true;
		changed = false;
	}

	/**
	 * Returns the string with all characters that are not numbers, letters, or
	 * spaces removed.
	 * 
	 * @param str
	 *            Input string
	 * @return Output string
	 */
	protected String reduceCharset(String str) {
		return str.replaceAll("[^A-Za-z0-9 ]+", "");
	}

	/**
	 * Returns the unique identifier used in the database to identify the
	 * object. Objects that are not in the database will return a -1.
	 * 
	 * @return Unique identifier of object, or -1 if object DNE in DB
	 */
	public int getUniqueIdentifier() {
		if (isInDatabase()) {
			return databaseID;
		} else {
			return -1;
		}
	}

	/**
	 * Set the unique identifier to match what exists in the database.
	 * 
	 * @param uniqueID
	 *            id of object
	 */
	protected void setUniqueIdentifier(int uniqueID) {
		databaseID = uniqueID;
	}

	/**
	 * Return true if object is known to exist in database, and false otherwise.
	 * 
	 * @return true if object in DB, false otherwise
	 */
	protected boolean isInDatabase() {
		return validID;
	}

	/**
	 * Return an array of Strings corresponding to the column names of your
	 * table. Only include fields which require you to update them (i.e. <b>do
	 * not include the unique identifier field if it auto-increments</b>)
	 * <p>
	 * The order here affects the order in {@link #loadPreparedStatement(PreparedStatement)}.
	 * <p>
	 * <i>Example code:</i>
	 * <ol>
	 * <li> String[] out = new String[2]; // 2 = number of fields
	 * <li> out[0] = "ROOMNUMBER";
	 * <li> out[1] = "ROOMCOMMENT";
	 * <li> return out;
	 * </ol>
	 * Note: You are loading an array here, which is zero-indexed.  The
	 * loadPreparedStatement command uses a notation which is one-indexed,
	 * so <b>be careful!</b>
	 * 
	 * @return Array of Strings representing user-editable field names
	 * @see #getFieldNames()
	 */
	protected abstract String[] getFieldNames();

	
	/**
	 * Within this function, you should write the function calls you need to
	 * load a PreparedStatement.
	 * <p>
	 * <i>Example code:</i>
	 * <ol>
	 * <li>stmt.setInt(1, numberOfNights);<br>
	 * <li>stmt.setString(2, guestName);<br>
	 * <li>// etc...
	 * </ol>
	 * 
	 * The numbers start with 1 and go up. They refer to field names. The order
	 * of the field names is determined in the {@link #getFieldNames()} method you
	 * implement.
	 * <p>
	 * Note that these are one-indexed, whereas they were zero-indexed in getFieldNames.
	 * 
	 * @param stmt The PreparedStatement.
	 * @see #getFieldNames()
	 */
	protected abstract void loadPreparedStatement(
			PreparedStatement stmt) throws SQLException;

	/**
	 * Return the field name of the unique identifier in your table.
	 * <p>
	 * <i>Example code:</i>
	 * <ol>
	 * <li> return "ROOM_DB_ID";
	 * </ol>
	 * 
	 * @return Unique identifies field name
	 */
	protected abstract String getUniqueIdentifierFieldName();

	/**
	 * Return a String containing the name of the table this object exists in.
	 * <p>
	 * <i>Example code:</i>
	 * <ol>
	 * <li> return "ROOMS";
	 * </ol>
	 * 
	 * @return String of Table Name
	 */
	protected abstract String getTableName();

	/**
	 * This method should load your object fields from the ResultSet rs with
	 * multiple get calls. You can use column indices or column titles to choose
	 * columns. The example uses column titles.
	 * <p>
	 * <i>Example:</i>
	 * <ol>
	 * <li>this.roomNumber = rs.getInt("ROOMNUMBER");
	 * <li>this.roomComment = rs.getString("ROOMCOMMENT");
	 * </ol>
	 * <b>Do not</b> worry about the unique identifier, it is automatically
	 * saved for you.
	 * 
	 * @param rs
	 * @throws SQLException
	 */
	protected abstract void loadFromDatabase(ResultSet rs) throws SQLException;

	/**
	 * Return boolean that indicates whether duplicates should be allowed to
	 * exist, or whether objects with the same field values should be mapped to
	 * the same database row entry.
	 * <p>
	 * <i> Example code:</i>
	 * <ol>
	 * <li> return false; // Do not allow duplicates
	 * </ol>
	 * 
	 * @return
	 */
	protected abstract boolean allowDuplicateRows();
	
	/**
	 * Commit changes made to the database, provided a change has been made
	 * (indicated by calling markAsChanged()).
	 * <p>
	 * <b>Postconditions:</b>
	 * <ul>
	 * <li>Object is in database.
	 * <li>The object's unique identifier is valid.
	 * <li>Object is marked as synched with database.
	 * </ul>
	 */
	public void commitToDatabase() {

		ResultSet rs = null;

		// Get the database access object
		DAO localDAO = DAOnoGUI.getDAO();

		try {
			if(isMarkedForDeletion()&&(!isDeleted)) {
				if(isInDatabase()) {
					removeFromDatabase();
				}
				isDeleted = true;
			// Check to see if object's existence in the database has already
			// been verified, and if so, update the record.
			} else if (isInDatabase()) {
				// Build update string
				StringBuilder updateString = new StringBuilder("UPDATE "
						+ getTableName() + " SET");
				String[] fieldNames = getFieldNames();
				for (int i = 0; i < fieldNames.length; i++) {
					if (i > 0) {
						updateString.append(',');
					}
					updateString.append(" " + fieldNames[i] + "=?");
				}
				updateString.append(" WHERE " + getUniqueIdentifierFieldName()
						+ "=" + getUniqueIdentifier() + ";");

				// Prepare, load, and execute update statement
				PreparedStatement updateStmt = localDAO
						.generatePreparedStatement(updateString.toString());
				loadPreparedStatement(updateStmt);
				localDAO.executePreparedUpdate(updateStmt);
			} else {
				// The object has not been verified to exist in the database.
				// If allowDuplicateRows() is false, then we should check to
				// see if a duplicate exists.
				boolean needNewEntry;
				
				// Retrieve filed names to update
				String[] fieldNames = getFieldNames();
				
				// Build checking string (unconditionally needed)
				StringBuilder checkIDString = new StringBuilder("SELECT "
						+ getUniqueIdentifierFieldName() + " FROM "
						+ getTableName() + " WHERE ");
				for (int i = 0; i < fieldNames.length; i++) {
					if (i > 0) {
						checkIDString.append("AND ");
					}
					checkIDString.append(fieldNames[i] + "=? ");
				}
				checkIDString.append(';');
				
				// Prepare checking query
				PreparedStatement checkIDStmt = localDAO
						.generatePreparedStatement(checkIDString.toString());
				loadPreparedStatement(checkIDStmt);
				
				if(allowDuplicateRows()) {
					needNewEntry = true;
				} else {
					// Execute select statement
					rs = localDAO.executePreparedSelect(checkIDStmt);
					// Returns false if no entry currently exists
					needNewEntry = !rs.next();
				}
				// If we need a new entry, then create one and retrieve it's ID
				if (needNewEntry) {
					// Insert the new record into the database
					// Build the query string
					StringBuilder insertString = new StringBuilder(
							"INSERT INTO " + getTableName() + " (");
					for (int i = 0; i < fieldNames.length; i++) {
						if (i > 0) {
							insertString.append(", ");
						}
						insertString.append(fieldNames[i]);
					}
					insertString.append(") VALUES (");
					for (int i = 0; i < fieldNames.length; i++) {
						if (i > 0) {
							insertString.append(',');
						}
						insertString.append('?');
					}
					insertString.append(");");

					// Prepare, load, and execute insert statement
					PreparedStatement insertStmt = localDAO
							.generatePreparedStatement(insertString.toString());
					loadPreparedStatement(insertStmt);
					localDAO.executePreparedUpdate(insertStmt);

					// Execute the check ID query again to obtain new unique id
					rs = localDAO.executePreparedSelect(checkIDStmt);
					while(!rs.isLast())
					{
						rs.next();
					}
				}
				// At this point rs contains the row from the database,
				// but we do not know the id. Save the ID.
				setUniqueIdentifier(rs.getInt(getUniqueIdentifierFieldName()));
				
				// Indicate that the value of this object was read from the DB
				markAsSynchedWithDatabase();
			}
		} catch (SQLException e) {
			// TODO [Ben] Auto-generated catch block
			e.printStackTrace();
		}
		
		// For when the DAO was opened locally
		DAOnoGUI.closeDAO();
	}
	
	/**
	 * This function fetches a full table from the database.  DBRowList
	 * then uses this table to load rows into objects.
	 * 
	 * @return
	 */
	protected ResultSet fetchTableFromDatabase() {
		// Build checking string
		String checkIDString = "SELECT * FROM " + getTableName() + ";";
		// Query database
		DAO localDAO = DAOnoGUI.getDAO();
		ResultSet rs = localDAO.getResult(checkIDString);
		DAOnoGUI.closeDAO();
		return rs;
	}
	
	/**
	 * Returns whether the object has been marked for deletion from the
	 * database.
	 * 
	 * @return
	 */
	public boolean isMarkedForDeletion() {
		return toBeDeleted;
	}
	
	/**
	 * This method returns true if the object should be displayed in a JList and
	 * false if it should not.  Override this method to change the behavior of what
	 * is displayed in a JList.
	 * 
	 * @return
	 */
	public boolean isDisplayable() {
		return isMarkedForDeletion();
	}
	
	/**
	 * Mark this object to be removed from the database on the next commit.
	 * <p>
	 * If this object is part of a DBRowList, then it will be removed from
	 * the collection, otherwise it will remain as a dead object.
	 * <p>
	 * <b>Postcondition:</b> Object will be marked for deletion.
	 */
	public void markToBeDeleted() {
		toBeDeleted = true;
	}
	
	/**
	 * Remove this object from the database.  This does not delete the object.
	 * Committing changes will re-add this object!!!
	 * 
	 * Use markForDeletion(), which deletes on next commit.
	 * This function was deprecated because it does not match the behavior of the rest of the 
	 * class.
	 */
	private void removeFromDatabase() {
		if(isInDatabase()) {
			DAO localDAO = DAOnoGUI.getDAO();
			String query = "DELETE FROM " + getTableName() + " WHERE " + getUniqueIdentifierFieldName() + "=" + getUniqueIdentifier();
			localDAO.updateCommand(query);
			DAOnoGUI.closeDAO();
		}
	}
	 

}
